package com.tiantian.poi;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;

/**
 * poi报表导出构建器
 * 
 * @author 王强
 * @time 2015年1月7日 上午9:11:01
 */
public class PoiReportBuilder {
	private Short titleHeight;// 标题高
	private Short subTitleHeight;// 子标题高度
	private Short headHeight;// 表头高度
	private Short bottomHeight;// 底部高度
	private Short columnHeight;// 单元格高度
	private int rowIndex;// 行数
	private HSSFWorkbook wb;
	private HSSFSheet sh;
	private HSSFCellStyle titleStyle;// 标题样式
	private HSSFCellStyle subTitleStyle;// 副标题样式
	private HSSFCellStyle headerStyle;// 表头样式
	private HSSFCellStyle columnStyle;// 单元格样式
	private HSSFCellStyle bottomStyle;// 底部样式
	public static int pageSize = 10000;
	private boolean isPageTitle;// 分页的时候是否需要表头以及子标题

	private int subTitleCospan;// 标题合并格
	private PoiColumn[][] headerColumns;
	private String[] subTitle;

	/**
	 * 传入路径信息
	 * 
	 * @param basePath
	 *            基础路径
	 * @param fileName
	 *            文件名
	 */
	public PoiReportBuilder() {
		this.rowIndex = 0;
		builderInit();
	}

	/**
	 * builder初始化
	 */
	private void builderInit() {
		wb = new HSSFWorkbook();
		sh = wb.createSheet();
		headerStyle = getColumnHeadStyle();
		titleStyle = getTitleStyle();
		subTitleStyle = getSubTitleStyle();
		bottomStyle = getBottomStyle();
		columnStyle = getColumnStyle();
	}

	/**
	 * 写入标题
	 * 
	 * @param columns
	 * @return
	 */
	public PoiReportBuilder appendBottom(int colSpan, String bottom) {
		HSSFRow row = sh.createRow(rowIndex % pageSize);
		if (titleHeight != null) {
			row.setHeight(bottomHeight);
		}
		for (int i = 1; i < colSpan + 1; i++) {
			createStringCell(row, i, "", bottomStyle);
		}
		createStringCell(row, 0, bottom, bottomStyle);
		CellRangeAddress address = new CellRangeAddress(rowIndex, rowIndex, 0, colSpan);
		sh.addMergedRegion(address);

		rowIndex++;
		if (rowIndex % pageSize == 0) {
			sh = wb.createSheet();
		}
		return this;
	}

	/**
	 * 写入标题
	 * 
	 * @param columns
	 * @return
	 */
	public PoiReportBuilder appendTitle(int colSpan, String title) {
		HSSFRow row = sh.createRow(rowIndex % pageSize);
		if (titleHeight != null) {
			row.setHeight(titleHeight);
		}
		createStringCell(row, 0, title, titleStyle);
		CellRangeAddress address = new CellRangeAddress(rowIndex, rowIndex, 0, colSpan);
		sh.addMergedRegion(address);

		rowIndex++;
		if (rowIndex % pageSize == 0) {
			sh = wb.createSheet();
		}
		return this;
	}

	/**
	 * 写入子标题
	 * 
	 * @param columns
	 * @return
	 */
	public PoiReportBuilder appendSubTitle(int colSpan, String... title) {
		if (title != null) {
			subTitle = title;
			subTitleCospan = colSpan;
			for (int i = 0; i < title.length; i++) {
				HSSFRow row = sh.createRow(rowIndex % pageSize);
				if (subTitleHeight != null) {
					row.setHeight(subTitleHeight);
				}
				createStringCell(row, 0, title[i], subTitleStyle);
				CellRangeAddress address = new CellRangeAddress(rowIndex, rowIndex, 0, colSpan);
				sh.addMergedRegion(address);
				rowIndex++;
				if (rowIndex % pageSize == 0) {
					sh = wb.createSheet();
				}
			}
		}

		return this;
	}

	/**
	 * 写入标题(多表头)
	 * 
	 * @param columns
	 * @return
	 */
	public PoiReportBuilder appendHeader(PoiColumn[][] columns) {
		if (columns != null) {
			this.headerColumns = columns;
			int sumColumn = 0;
			for (int j = 0; j < columns[0].length; j++) {
				sumColumn += columns[0][j].getColumn();
			}
			// 数组初始化时默认值为0，该位置也默认为显示,非合并
			int[][] index = new int[columns.length][sumColumn];
			for (int i = 0; i < columns.length; i++) {
				int minColumn = 0;
				if (i > 0) {
					for (int t = 0; t < index[i - 1].length; t++) {
						if (index[i - 1][t] != 0) {
							minColumn = t - 1;
							break;
						}
					}
				}
				for (int j = 0; j < columns[i].length; j++) {
					PoiColumn column = columns[i][j];
					if (column.getRowspan() > 1) {
						for (int r = 0; r < column.getRowspan() - 1; r++) {
							// 有跨行的情况下，除当前行之外，其他行不显示标题
							index[i + r + 1][minColumn] = 1;
							if (column.getColumn() > 1) {
								for (int k = 0; k < column.getColumn() - 1; k++) {
									index[i + r + 1][k + minColumn + 1] = 1;
								}
							}
						}
					}
					if (column.getColumn() > 1) {
						for (int r = 0; r < column.getColumn() - 1; r++) {
							index[i][r + minColumn + 1] = 1;
						}
					}
					minColumn += column.getColumn();
				}
			}
			for (int i = 0; i < index.length; i++) {
				HSSFRow row = sh.createRow(rowIndex % pageSize);
				if (headHeight != null) {
					row.setHeight(headHeight);
				}
				int min = 0;
				for (int j = 0; j < index[i].length; j++) {
					if (index[i][j] == 0) {
						PoiColumn column = columns[i][min];
						sh.setColumnWidth(j, (column.getWidth() * 35));
						createStringCell(row, j, column.getName(), headerStyle);
						min++;
						int maxColumn = j + column.getColumn() - 1;
						CellRangeAddress address = new CellRangeAddress(rowIndex, rowIndex + column.getRowspan() - 1, j, maxColumn);
						sh.addMergedRegion(address);
					} else {
						createStringCell(row, j, "", headerStyle);
					}
				}
				rowIndex++;
				if (rowIndex % pageSize == 0) {
					sh = wb.createSheet();
				}
			}
		}
		return this;
	}

	/**
	 * 写入标题
	 * 
	 * @param columns
	 * @return
	 */
	public PoiReportBuilder appendHeader(PoiColumn... columns) {
		return this.appendHeader(new PoiColumn[][] { columns });
	}

	public PoiReportBuilder appendHeader(String... columns) {
		PoiColumn[] list = new PoiColumn[columns.length];
		for (int i = 0; i < columns.length; i++) {
			list[i] = new PoiColumn(columns[i]);
		}
		return this.appendHeader(list);
	}

	/**
	 * 分页的时候写入标题
	 * 
	 * @param columns
	 * @return
	 */
	public void appendHeader() {
		if (this.isPageTitle) {
			this.appendSubTitle(subTitleCospan, subTitle);
			for (PoiColumn[] columns : headerColumns) {
				if (columns != null) {
					HSSFRow row = sh.createRow(rowIndex % pageSize);
					if (headHeight != null) {
						row.setHeight(headHeight);
					}
					for (int cellnum = 0; cellnum < columns.length; cellnum++) {
						PoiColumn column = columns[cellnum];
						sh.setColumnWidth(cellnum, (column.getWidth() * 35));
						createStringCell(row, cellnum, column.getName(), headerStyle);
					}
					rowIndex++;
					if (rowIndex % pageSize == 0) {
						sh = wb.createSheet();
					}
				}
			}
		}
	}

	/**
	 * 写入数据
	 * 
	 * @param datas
	 * @return
	 */
	public PoiReportBuilder appendData(String... datas) {
		if (datas != null) {
			HSSFRow row = sh.createRow(rowIndex % pageSize);
			if (columnHeight != null) {
				row.setHeight(columnHeight);
			}
			for (int cellnum = 0; cellnum < datas.length; cellnum++) {
				String data = datas[cellnum];
				if (data != null) {
					columnStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
					createStringCell(row, cellnum, datas[cellnum], columnStyle);
				} else {
					columnStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
					createStringCell(row, cellnum, "", columnStyle);
				}
			}
			rowIndex++;
			if (rowIndex % pageSize == 0) {
				sh = wb.createSheet();
				appendHeader();
			}
		}
		return this;
	}

	/**
	 * 写入文件
	 * 
	 * @param basePath
	 * @param fileName
	 * @return
	 * @throws IOException
	 */
	public String write(String basePath, String fileName) throws IOException {
		// 文件名称路径操作
		createDir(basePath);
		fileName = buildFileName(fileName);
		String path = basePath + File.separator + fileName;
		// 报表写入
		FileOutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream(path);
			wb.write(fileOut);
		} finally {
			fileOut.close();
		}
		return fileName;
	}
	
	public void write(OutputStream outputStream) throws IOException{
		wb.write(outputStream);
	}

	/**
	 * 设置表头样式
	 * 
	 * @return
	 */
	private HSSFCellStyle getColumnHeadStyle() {
		HSSFCellStyle cellStyle = wb.createCellStyle();
		// 字体
		HSSFFont font = wb.createFont();
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints((short) 9);
		font.setFontName("宋体");
		cellStyle.setFont(font);
		// 居中
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		cellStyle.setWrapText(true);

		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle.setTopBorderColor(HSSFColor.BLACK.index);

		return cellStyle;
	}

	/**
	 * 设置单元格样式
	 * 
	 * @return
	 */
	private HSSFCellStyle getColumnStyle() {

		HSSFCellStyle cellStyle = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		// 设置字体大小
		font.setFontHeightInPoints((short) 9);
		font.setFontName("宋体");
		cellStyle.setFont(font);
		// 居中
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		// 设置自动换行
		cellStyle.setWrapText(true);

		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle.setTopBorderColor(HSSFColor.BLACK.index);

		return cellStyle;
	}

	private HSSFCellStyle getBottomStyle() {

		HSSFCellStyle cellStyle = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		// 设置字体大小
		font.setFontHeightInPoints((short) 9);
		font.setFontName("宋体");
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		cellStyle.setFont(font);
		// 居中
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		// 设置自动换行
		cellStyle.setWrapText(true);

		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle.setTopBorderColor(HSSFColor.BLACK.index);

		return cellStyle;
	}

	/**
	 * 标题样式
	 * 
	 * @return
	 */
	private HSSFCellStyle getTitleStyle() {
		HSSFCellStyle titleStyle = wb.createCellStyle();
		// 字体
		HSSFFont font = wb.createFont();
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints((short) 17);
		font.setFontName("宋体");
		titleStyle.setFont(font);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		titleStyle.setWrapText(true);
		return titleStyle;
	}

	/**
	 * 副标题样式
	 * 
	 * @return
	 */
	private HSSFCellStyle getSubTitleStyle() {
		HSSFCellStyle subTitleStyle = wb.createCellStyle();
		// 字体
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 10);
		font.setFontName("宋体");
		subTitleStyle.setFont(font);
		// 居中
		// subTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		subTitleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		subTitleStyle.setWrapText(true);
		return subTitleStyle;
	}

	/**
	 * 创建单元格样式
	 * 
	 * @param row
	 *            当前行
	 * @param column
	 *            列表树
	 * @param value
	 *            写入值
	 * @param columnStyle2
	 *            单元格样式
	 * @return
	 */
	private HSSFCell createStringCell(HSSFRow row, int column, String value, HSSFCellStyle columnStyle) {
		HSSFCell cell = row.createCell(column);
		// 文本格式
		cell.setCellType(XSSFCell.CELL_TYPE_STRING);
		// cell.setCellType(HSSFCell.ENCODING_UTF_16);
		if (columnStyle != null) {
			cell.setCellStyle(columnStyle);
		}

		// 写入内容
		if (value != null) {
			cell.setCellValue(value);
		}
		return cell;
	}

	/**
	 * 构建动态文件名称
	 * 
	 * @param fileName
	 *            默认名称
	 * @return
	 */
	private String buildFileName(String fileName) {
		String suffix = ".xls";
		return new StringBuffer(fileName == null ? "" : fileName).append("_").append(System.currentTimeMillis()).append(suffix).toString();
	}

	/**
	 * 创建路径（如果该路径不存在）
	 * 
	 * @param basePath
	 */
	private void createDir(String basePath) {
		File file = new File(basePath);
		if (!file.exists()) {
			file.mkdirs();
		}
	}

	public Short getTitleHeight() {
		return titleHeight;
	}

	public void setTitleHeight(Short titleHeight) {
		this.titleHeight = titleHeight;
	}

	public Short getSubTitleHeight() {
		return subTitleHeight;
	}

	public void setSubTitleHeight(Short subTitleHeight) {
		this.subTitleHeight = subTitleHeight;
	}

	public Short getHeadHeight() {
		return headHeight;
	}

	public void setHeadHeight(Short headHeight) {
		this.headHeight = headHeight;
	}

	public Short getBottomHeight() {
		return bottomHeight;
	}

	public void setBottomHeight(Short bottomHeight) {
		this.bottomHeight = bottomHeight;
	}

	public Short getColumnHeight() {
		return columnHeight;
	}

	public void setColumnHeight(Short columnHeight) {
		this.columnHeight = columnHeight;
	}

	public boolean isPageTitle() {
		return isPageTitle;
	}

	public void setPageTitle(boolean isPageTitle) {
		this.isPageTitle = isPageTitle;
	}

}
